package com.sq.partner.manager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradNoCarProdDao;
import com.sq.partner.manager.mybatis.model.NocarProd;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class TradNoCarProdDaoImpl extends BaseDaoImpl implements ITradNoCarProdDao {
	private static Logger logger = LogUtil.getLog(TradNoCarProdDaoImpl.class);

	@Override
	public Page<NocarProd> queryPageForNocarInsure(Page<NocarProd> page)
			throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.PROD_CODE AS PRODCODE,T.PROD_NAME AS PRODNAME,T.PROD_DESC AS PRODDESC,T.PROD_TYPE AS PRODTYPE, W.CODE_NAME AS PRODTYPEDSCP,T.STATUS AS STATUS, TO_CHAR(T.INSERT_TIME, 'yyyy-MM-dd HH24:MI:SS') AS INSERTTIME, NVL(T.IS_POOL, 'N') AS ISPOOL,MAX_DELAY_DAY AS MAXDELAYDAY,MAX_DAYS AS MAXDAYS,MIN_DAYS AS MINDAYS,AGENT_TYPE AS AGENTTYPE,T1.EPOLICYTYPE,T2.ECONFIRMTYPE "+
	  "FROM T_NOCAR_PROD T  LEFT JOIN （select TRAD_PROD_CODE,PROP_VALUE epolicyType from  T_TRAD_PROD_PROP where  prop_type='epolicy'）T1 ON T1.TRAD_PROD_CODE = T.PROD_CODE   LEFT JOIN （select TRAD_PROD_CODE, min(PROP_VALUE) ECONFIRMTYPE  from T_TRAD_PROD_PROP  where prop_type = 'econfirm' group by trad_prod_code）T2 ON T2.TRAD_PROD_CODE = T.PROD_CODE INNER JOIN T_GATEWAY_DICT W ON T.PROD_TYPE = W.CODE  WHERE 1 = 1  AND T.STATUS = '1' ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NocarProd nocarProd= page.getType();
			if(StrUtil.isNotEmpty(nocarProd.getProdCode())){
				sqlbuf.append(" AND PROD_CODE like ?");
				list.add(nocarProd.getProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(nocarProd.getProdName())){
				sqlbuf.append(" AND PROD_NAME like ?");
				list.add(nocarProd.getProdName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(nocarProd.getVstartDate())){
				sqlbuf.append(" AND INSERT_TIME >= to_date(? ,'yyyy-mm-dd')");
				list.add(nocarProd.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(nocarProd.getVendDate())){
				sqlbuf.append(" AND INSERT_TIME  < to_date(? ,'yyyy-mm-dd') +1");
				list.add(nocarProd.getVendDate());
				islike.add(false);
			}
			sqlbuf.append(" order by INSERT_TIME desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	@Override
	public Page<NocarProd> queryPage(Page<NocarProd> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.PROD_CODE AS PRODCODE,T.PROD_NAME AS PRODNAME,T.PROD_DESC AS PRODDESC,T.PROD_TYPE AS PRODTYPE, "
					+ "W.CODE_NAME AS PRODTYPEDSCP,T.STATUS AS STATUS, TO_CHAR(T.INSERT_TIME, 'yyyy-MM-dd HH24:MI:SS') AS INSERTTIME, "
					+ "NVL(T.IS_POOL, 'N') AS ISPOOL ,MAX_DELAY_DAY AS MAXDELAYDAY,MIN_DELAY_DAY AS  MINDELAYDAY,MAX_CANCEL_DAY AS LONGESTCANCELDAY,"
					+ "MAX_DAYS AS MAXDAYS,MIN_DAYS AS MINDAYS,AGENT_TYPE AS AGENTTYPE,T.CON_INSU_CODE AS CONINSUCODE,T.PPP_UNIT AS PPPUNIT ,T.MIN_DELAY_UNIT AS MINDELAYUNIT,T.TGT_TYPE AS TGTTYPE "+
					"FROM T_NOCAR_PROD T INNER JOIN T_GATEWAY_DICT W ON T.PROD_TYPE = W.CODE  WHERE 1 = 1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NocarProd nocarProd= page.getType();
			if(StrUtil.isNotEmpty(nocarProd.getProdCode())){
				sqlbuf.append(" AND PROD_CODE like ?");
				list.add(nocarProd.getProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(nocarProd.getProdName())){
				sqlbuf.append(" AND PROD_NAME like ?");
				list.add(nocarProd.getProdName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(nocarProd.getStatus())){
				sqlbuf.append(" AND STATUS=?");
				list.add(nocarProd.getStatus());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(nocarProd.getVstartDate())){
				sqlbuf.append(" AND INSERT_TIME >= to_date(? ,'yyyy-mm-dd')");
				list.add(nocarProd.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(nocarProd.getVendDate())){
				sqlbuf.append(" AND INSERT_TIME  < to_date(? ,'yyyy-mm-dd') +1");
				list.add(nocarProd.getVendDate());
				islike.add(false);
			}
			sqlbuf.append(" order by INSERT_TIME desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public void saveNoCarTradProd(NocarProd noCartradProd) throws Exception {
		// TODO Auto-generated method stub
		this.add("insertNocarProd", noCartradProd);
	}



	@Override
	public NocarProd findMaxNocarTradProdCode(NocarProd noCartradProd)
			throws SQLException {
		// TODO Auto-generated method stub
		return  (NocarProd) this.query("queryMaxNoCarTradProdCode", noCartradProd) ;
	}

	@Override
	public NocarProd findMaxRiskProdIndex(NocarProd noCartradProd)
			throws SQLException {
		// TODO Auto-generated method stub
		return  (NocarProd) this.query("queryMaxRiskProdIndexByCode", noCartradProd) ;
	}


	@Override
	public NocarProd queryNocarTradProdInstById(String tradProdCode)
			throws SQLException {
		// TODO Auto-generated method stub
		return (NocarProd) this.query("queryNocarTradProdInstById", tradProdCode) ;
	}



	@Override
	public void updateNocarProd(NocarProd noCarProd) throws Exception {
		// TODO Auto-generated method stub
		this.update("updateNocarProdNotNull", noCarProd);
	}
	


	@Override
	public void deleteNocarProd(String prodCode) throws Exception {
		// TODO Auto-generated method stub
		this.delete("delNocarProdById", prodCode);
	}

	@Override
	public boolean isExistProdPlan(String prodCode) throws SQLException {
		// TODO Auto-generated method stub
		Integer isLink = (Integer) this.query("isExistProdPlanCountByProdCode", prodCode); 

		if((isLink!=null &&  Integer.valueOf(isLink)>0))
		{
			return true;
		}
		return false;
	}
	@Override
	public  List<Map<String, Object>> searchNoCarProdsByprodCode(Map<String, Object> map) throws SQLException {
		return  this.queryList("searchNoCarProdsByprodCode", map);
	}
	
}
